
insert into crf.build_template(id, create_user_id, create_time, modify_user_id, modify_time, isdeleted, tenant_id, 
	[name], scheme_no, version_number, version_date, publish_enum)
select LEFT(itv.id, 25), '00fj9yinn2cb8d3np0sik2kp9', '2022-09-17', '00fj9yinn2cb8d3np0sik2kp9', '2022-09-17', 0, '00fj9yhj8deu8d3np0vcb59kh', 
	itv.fsmc, '', '', '2022-09-17', ''
from itv

-- delete from crf.build_template where id in (select LEFT(itv.id, 25) from itv)

GO
GO

insert into crf.build_step(id, create_user_id, create_time, modify_user_id, modify_time, isdeleted, tenant_id, 
	proj_name, step_enum, has_auto_index, child_index_enum, remarks, publish_enum, write_mode)
select LEFT(f.id, 25), '00fj9yinn2cb8d3np0sik2kp9', '2022-09-17', '00fj9yinn2cb8d3np0sik2kp9', '2022-09-17', 0, '00fj9yhj8deu8d3np0vcb59kh',
	f.formname, 'A', '0', 'a', '', 'a', 'a'
from edcform f

-- delete from crf.build_step where id in (select LEFT(e.id, 25) from edcform e)

GO
GO

insert into crf.build_template_map_step (id, build_template_id, build_step_id, [title], index_val)
select RIGHT(d.id, 12) + RIGHT(d.formid, 13), LEFT(d.id, 25), LEFT(d.formid, 25), (select top 1 f.formname from edcform f where f.id = d.formid) AS [title], ROW_NUMBER() OVER(order by d.id, d.formid) + 100
from (
	select itv.id, d.col AS formid
	from itv
		cross apply (
			select * from f_splitSTR(itv.formid, ',')
		) d
) d

-- delete from crf.build_template_map_step where LEFT(id, 12) in (select RIGHT(itv.id, 12) from itv)

GO
GO

insert into crf.build_item (id, build_step_id, clild_group_name, index_val, title, input_enum, releate_enum, enum_values, lab_result_enum, unit_name, is_required, default_value, date_mode, select_model_enum)
select LEFT(d.id, 25), LEFT(d.formid, 25), null, CONVERT(int, d.sort), d.attbcn, 
	(case when d.ctltype = 'radiobox' then 'select' 
	 when d.ctltype = 'textarea' then 'text'
	 when d.ctltype = 'input' then 'text'
	 when d.ctltype = 'select' then 'select'
	 when d.ctltype = 'dateselect' then 'date'
	 when d.ctltype = 'selectlist' then 'select'
	 when d.ctltype = 'checkbox' then 'select'
	 else '' end),
	null,
	STUFF((select ',' + dd.[label] from dictDetail dd where dd.[type] = d.dicttype order by CONVERT(int, dd.sort) FOR XML PATH('')), 1, 1, ''),
	null,
	d.remarks,
	isnull((select top 1 1 from edcValidDetail vd where vd.edcattbid = d.id and vd.basevali like '%required%'), 0),
	null,
	case when d.ctltype != 'dateselect' then null 
		when d.datatype = 'datemonth' then 'yM'
		when d.datatype = 'datetime' then 'yMd'
		when d.datatype = 'datehour' then 'yMdHm'
		when d.datatype = 'minute' then 'yMdHm'
		when d.datatype = 'time' then 'yMdHm'
		when d.datatype = 'timetime' then 'Hm'
		when d.datatype = 'timeminute' then 'Hm'
		else null end,
	(case when d.ctltype = 'radiobox' then 'single'
		when d.ctltype = 'select' then 'single'
		when d.ctltype = 'input' then 'single'
		when d.ctltype = 'textarea' then 'multiple'
		when d.ctltype = 'selectlist' then 'multiple'
		when d.ctltype = 'checkbox' then 'multiple'
	else 'single' end)
from edcAttb d
where d.ctltype IN ('radiobox', 'textarea', 'input', 'select', 'dateselect') and (d.parentid is null OR d.parentid = '')

-- delete from crf.build_item where id in (select left(a.id, 25) from edcAttb a)

GO
GO

insert into crf.build_item_visible(id, build_step_id, build_item_id, compare_mode, item_value, field_id)
select LEFT(d.parentId, 23) + RIGHT('0' + CONVERT(nvarchar(2), (ROW_NUMBER() OVER(partition by d.parentId ORDER BY d.kId DESC))), 2),
	(select top 1 LEFT(a2.formId, 25) from edcAttb a2 where a2.id = d.parentId),
	LEFT(d.propid, 25),
	(case when d.soper = '=' and d.dval = 0 then '!='
		  when d.soper = '=' and d.dval = 1 then '=='
	 else null end),
	ISNULL(STUFF((select ',' + dd.[label] from dictDetail dd where dd.[value] = d.sval and  dd.[type] = a.dicttype order by CONVERT(int, dd.[value]) FOR XML PATH('')), 1, 1, ''), d.sval),
	LEFT(d.sattbid, 25)
	-- , (select f.formname from edcform f where exists(select top 1 1 from edcAttb a where a.formId = f.id and a.id = d.parentId))
from (
	select i.*, d.col AS propid
	from edcValidDetailItem i
		cross apply (
			select * from f_splitSTR(i.dattbid, ',')
		) d
) d
	left join edcAttb a ON a.id = d.parentId

-- delete from crf.build_item_visible where left(id, 23) in (select left(i.parentId, 23) from edcValidDetailItem i)

GO
GO

insert into crf.build_item_rule(id, build_step_id, build_item_id, min_value, max_value, decimal_point_count, max_length, begin_date, end_date, rule_des, can_not_lead_time)
select LEFT(d.edcattbid, 25), LEFT(d.edcformid, 25), LEFT(d.edcattbid, 25), CONVERT(int, d.minval), CONVERT(int, d.maxval), 
	case when (d.ctltype != 'input' OR d.datatype != 'number') then null
		when d.basevali like '%digits=\"true\"%' then 0
		when d.rtd like '%isRtD1=\"true\"%' then 1
		when d.rtd like '%isRtD2=\"true\"%' then 2
		when d.rtd like '%isRtD3=\"true\"%' then 3
		else null end,
	CONVERT(int, d.[maxlength]),
	null, null, 
	d.attbcn,
	case when d.ctltype != 'dateselect' then null
		when d.basevali like '%systime%' then 1
		else 0 end
from edcValidDetail d
where (d.basevali is not null And d.basevali <> '' and d.basevali <> 'required') OR (d.rtd IS NOT NULL and d.rtd <> '') OR d.[minlength] > 0 OR d.[maxlength] > 0 OR d.maxval > 0 OR d.minval > 0

-- delete from crf.build_item_rule where id in (select LEFT(d.edcattbid, 25) from edcValidDetail d)

GO

--truncate table crf.build_item
--truncate table crf.build_item_rule
--truncate table crf.build_item_visible
--truncate table crf.build_relate_field
--truncate table crf.build_step
--truncate table crf.build_template
--truncate table crf.build_template_map_step
